# date: 2024/6/30  18:48
# user: blackcat

import os
import re
import sys
import openpyxl
import tkinter as tk
from tkinter import messagebox
from tkcalendar import Calendar
from openpyxl.styles import NamedStyle
from datetime import datetime
from openpyxl.styles import Font
from openpyxl.cell.text import InlineFont
from openpyxl.styles import Alignment
from openpyxl.cell.rich_text import TextBlock, CellRichText


def processing_strings(string):
    """
    处理发起人字符串，仅提取姓名
    :return:
    """
    # 去除末尾的回车符
    if string[-1] == '\r':
        string = string[:-1]

    # 取/前的字符串
    splitted_string = string.split('/', 1)
    if len(splitted_string) > 1:
        string1 = splitted_string[0] + '/'
    else:
        string1 = ""
    # print(string1)

    # 取' '后的字符串
    splitted_string = string.split(' ', 1)
    if len(splitted_string) > 1:
        string2 = ' ' + splitted_string[1]
    else:
        string2 = ""

    # print(string2)

    # 分别去除前后字符串
    def remove_substring(main_string, to_remove):
        # print('去除字符串成功')
        return main_string.replace(to_remove, '')

    main_str = string
    to_remove = string1
    main_str = remove_substring(main_str, to_remove)
    to_remove = string2
    result = remove_substring(main_str, to_remove)
    return result


def has_chg_start(tuple_data):
    """
    检测是否有变更工单，CHG开头
    :param tuple_data:
    :return:
    """
    return any(elem.value.startswith('CHG') for elem in tuple_data)


def has_inc_start(tuple_data):
    """
    检测是否有事件工单，INC开头
    :param tuple_data:
    :return:
    """
    return any(elem.value.startswith('INC') for elem in tuple_data)


def center_window(window, width=None, height=None):
    """
    将窗口居中显示在屏幕上。

    :param window: Tkinter窗口实例
    :param width: 窗口宽度（如果不指定，则使用窗口的当前请求宽度）
    :param height: 窗口高度（如果不指定，则使用窗口的当前请求高度）
    """
    if width is None:
        width = window.winfo_reqwidth()

    if height is None:
        height = window.winfo_reqheight()

        # 获取屏幕宽度和高度
    screen_width = window.winfo_screenwidth()
    screen_height = window.winfo_screenheight()

    # 计算窗口左上角的x和y坐标
    x = (screen_width // 2) - (width // 2)
    y = (screen_height // 2) - (height // 2)

    # 将窗口移动到指定位置
    window.geometry(f'{width}x{height}+{int(x)}+{int(y)}')


class WorkingDiary(object):

    def __init__(self):
        """
        设置工作路径
        """
        # 获取程序当前所在路径

        path = os.getcwd()
        # path = r'D:/0日报'
        os.chdir(path)
        # 默认date变量为空字符串（用于后续判断是否关闭弹窗）
        self.date = ''
        self.date2 = ''

        # 获取工作路径下excel的文件名
        self.filenames = [f for f in os.listdir(path) if f.endswith('.xlsx') or f.endswith('.xls')]

        self.workbook = openpyxl.load_workbook(self.filenames[0])  # 导出工单(默认其为首位)
        self.workbook2 = openpyxl.load_workbook(self.filenames[1])  # 匹配模板

        self.sheet = self.workbook.active
        self.sheet2 = self.workbook2['sheet1']
        self.sheet3 = self.workbook2['安防运维组']
        self.sheet4 = self.workbook2['配置参数']
        self.sheet5 = None

    def chose_date(self):
        root = tk.Tk()
        root.title('请选择日期')

        # 初始时居中窗口
        center_window(root)

        cal = Calendar(root, selectmode='day', date_pattern='m月/d日/yyyy年')
        cal.pack(padx=50, pady=50, fill='both', expand=True)

        def get_date():
            date = cal.get_date()
            date_string = date.split('/', 2)
            self.date = '{}{}'.format(str(date_string[0]), str(date_string[1]))
            self.date2 = '{}{}{}'.format(str(date_string[2]),str(date_string[0]), str(date_string[1]))
            print(f"选择的日期是：{date}")
            # print(f"选择的日是：{date_string[1]}")
            # print(f"选择的月是：{date_string[0]}")
            root.destroy()

        btn = tk.Button(root, text="确认选择", command=get_date)
        btn.pack(padx=10, pady=10)

        # 在窗口大小改变时重新居中
        root.bind('<Configure>', lambda e: center_window(root))

        # 窗口主循环
        root.mainloop()

        # 直接关掉日历弹窗没有选择日期，则直接退出程序
        if self.date == '':
            sys.exit()
        pass

    def match_infor(self):
        """
        匹配固定巡检单信息
        :return:
        """
        # 匹配模板工单列
        for j in self.sheet2['A']:
            # 仅匹配固定巡检单
            if j.row > self.sheet4['B'][0].value:
                break
            # 导出文档工单列，倒序匹配，对应巡检工单发布的先后时间
            for k in reversed(self.sheet['A']):
                num1 = k.row - 1
                num2 = j.row - 1

                # 第一次匹配到
                if k.value == j.value and self.sheet['H'][num1].value != '已匹配':
                    if k.value == '工单标题':
                        break
                    self.sheet2['B'][num2].value = self.sheet['B'][num1].value  # 工单编号
                    self.sheet2['C'][num2].value = self.sheet['C'][num1].value  # 工单类型
                    name = processing_strings(self.sheet['G'][num1].value)  # 提取发起人姓名
                    self.sheet2['D'][num2].value = name  # 发起人姓名
                    print('工单编号' + self.sheet['B'][num1].value + '匹配成功')
                    # 修改导出表中已匹配成功的工单并保存
                    self.sheet['H'][num1].value = '已匹配'
                    self.workbook.save(self.filenames[0])
                    break
                    pass
                pass
            pass
        for i in self.sheet2['B']:
            if i.value is None:
                i.value = '**********'
        self.workbook2.save(self.filenames[1])
        print('匹配模板保存成功')
        pass

    def collect_infor(self):
        """
        采集导出表的其他工单信息，追加到模板表
        :return:
        """
        num1 = 1
        num2 = self.sheet4['B'][0].value
        print(self.sheet2.max_row)
        # 删除固定行以外的行
        while self.sheet2.max_row > num2:
            self.sheet2.delete_rows(self.sheet2.max_row)
            print(self.sheet2.max_row)

        for i in self.sheet['A']:
            num3 = i.row - 1
            # 过滤掉已匹配到巡检工单、状态不是已完成/处理中的工单
            if self.sheet['H'][num3].value == '已匹配':
                continue
            elif self.sheet['F'][num3].value != '已完成' and self.sheet['F'][num3].value != '处理中':
                continue
            elif i.value == '工单标题':
                continue
            else:
                self.workbook2._current_row = self.sheet2.max_row
                print('工单标题' + i.value + '匹配成功')
                self.sheet['H'][num3].value = '已匹配'
                # 插入似乎不行，得用追加
                name = processing_strings(self.sheet['G'][num3].value)  # 提取发起人姓名
                values = [self.sheet['A'][num3].value, self.sheet['B'][num3].value, self.sheet['C'][num3].value, name, \
                          self.sheet['M'][num3].value]
                # 追加工单数据
                self.sheet2.append(values)
            pass
        self.workbook.save(self.filenames[0])
        self.workbook2.save(self.filenames[1])
        print('匹配模板保存成功')
        pass

    def create_sheet(self):
        """
        创建台账中的三个事件工单，有则创建，无则不创建
        :return:
        """
        # 删除sheet1、安防运维组和配置参数外的所有工作表单
        for sheet_name in self.workbook2.sheetnames:
            if sheet_name == 'sheet1' or sheet_name == '安防运维组' or sheet_name == '配置参数':
                continue
            else:
                self.workbook2.remove(self.workbook2[sheet_name])
                print('删除表：' + sheet_name + '成功')

        # 请求管理台账
        sheet_qqgl = self.workbook2.copy_worksheet(self.sheet2)
        for i in sheet_qqgl['B']:
            num = i.row
            if i.value == '工单编号':
                continue
            elif i.value == '':
                i.value = '***********'
            elif i.value.startswith('CHG') is True:
                sheet_qqgl.delete_rows(idx=num)
            elif i.value.startswith('INC') is True:
                sheet_qqgl.delete_rows(idx=num)
            else:
                continue
            pass
        sheet_qqgl.title = '请求管理'
        self.workbook2.save(self.filenames[1])
        print('创建请求管理表成功')

        # 有变更工单的情况
        is_chg = has_chg_start(self.sheet2['B'])
        if is_chg is True:
            sheet_chg = self.workbook2.copy_worksheet(self.sheet2)
            for i in sheet_chg['B']:
                num = i.row
                if i.value == '工单编号':
                    continue
                elif i.value == '':
                    sheet_chg.delete_rows(idx=num)
                elif i.value.startswith('CHG') is True:
                    continue
                else:
                    sheet_chg.delete_rows(idx=num)
                pass
            sheet_chg.title = '变更工单'
            self.workbook2.save(self.filenames[1])
            print('创建变更工单表成功')

        # 有事件工单的情况
        is_inc = has_inc_start(self.sheet2['B'])
        if is_inc is True:
            sheet_inc = self.workbook2.copy_worksheet(self.sheet2)
            for i in sheet_inc['B']:
                num = i.row
                if i.value == '工单编号':
                    continue
                elif i.value == '':
                    sheet_inc.delete_rows(idx=num)
                elif i.value.startswith('INC') is True:
                    continue
                else:
                    sheet_inc.delete_rows(idx=num)
                pass
            sheet_inc.title = '事件工单'
            self.workbook2.save(self.filenames[1])
            print('创建事件工单表成功')

    def fill_content(self):
        """
        更新日报的内容
        :return:
        """
        cell = self.sheet3['C1']
        new_string = CellRichText([''])
        for i in self.sheet2['A']:
            if i.value == '工单标题':
                continue
            else:
                num = i.row - 1
                if self.sheet2['E'][num].value is None:
                    raw_string = str(num) + '、工单号:' + str(self.sheet2['B'][num].value) + ',' + self.date + ',' \
                                 + self.sheet2['A'][num].value + '￥' + '(已闭环)\n'
                else:
                    raw_string = str(num) + '、工单号:' + str(self.sheet2['B'][num].value) + ',' + self.date + ',' \
                                 + self.sheet2['A'][num].value + '；' + str(
                        self.sheet2['E'][num].value) + '￥' + '(已闭环)\n'
                pass
            print('第{}内容追加成功'.format(num))
            # raw_string = replace_text_with_color(raw_string, "（已闭环）", Fore.GREEN)
            green = InlineFont(b=True, rFont='微软雅黑', sz=10, color='00b050')
            black = InlineFont(b=True, rFont='微软雅黑', sz=10)
            resout = re.split(r'\￥', raw_string)
            new_string = new_string + CellRichText([TextBlock(green, i) if i == "(已闭环)\n" else TextBlock(black, i)\
                                                    for i in resout])
            pass

        cell.value = new_string
        self.workbook2.save(self.filenames[1])

        # font = Font(bold=True, name='微软雅黑', size=10)
        # cell.font = font
        # self.workbook2.save(self.filenames[1])
        print('日报内容更新成功')
        pass

    def request_content(self):
        """
        更新请求管理表的格式与内容
        :return:
        """
        self.sheet5 = self.workbook2['请求管理']
        self.sheet5.delete_cols(3)
        self.sheet5.delete_cols(5)
        self.sheet5.insert_cols(3, 2)
        self.sheet5.insert_cols(1)
        self.sheet5.insert_cols(7, 2)
        self.sheet5['A'][0].value = '时间'
        self.sheet5['D'][0].value = '系统名称'
        self.sheet5['E'][0].value = '工单状态'
        self.sheet5['G'][0].value = '处理人'
        self.sheet5['H'][0].value = '运维分组'

        i = 1
        print(self.date2)
        date5 = datetime.strptime(self.date2, '%Y年%m月%d日')
        print(date5)
        #date5 = datetime.strftime(date5, '%Y/m/d')

        row_count = 0
        for cell in self.sheet5['B']:
            if cell.value is None:
                break
            row_count = row_count + 1
        while i < row_count:

            self.sheet5.cell(row=i+1, column=1, value=date5)
            self.sheet5['A'][i].number_format = 'yyyy/m/d'
            self.sheet5['A'][i].alignment = Alignment(horizontal='right')
            self.sheet5['D'][i].value = '日常工作'
            self.sheet5['E'][i].value = '已完成'
            self.sheet5['G'][i].value = ''
            self.sheet5['H'][i].value = '安防组'
            i = i + 1
        self.workbook2.save(self.filenames[1])
        print('更新请求管理表成功')
        pass
if __name__ == '__main__':
    index = 0
    result = ''
    # 没有初始化成功，则判定问题为正在打开表格
    try:
        ts = WorkingDiary()
        index = index + 1
    finally:
        if index == 0:
            master = tk.Tk()
            master.withdraw()
            messagebox.showinfo(title="错误", message="请关闭所有已打开的表格！")

    # 选择日期
    ts.chose_date()

    try:
        # 匹配固定巡检工单信息
        ts.match_infor()
        string1 = '匹配固定巡检工单信息成功\n'
        index = index + 1
        result = result + string1
        # 汇集其他类型工单
        ts.collect_infor()
        string2 = '追加其他工单内容成功\n'
        index = index + 1
        result = result + string2
        # 分别生成台账表单
        ts.create_sheet()
        string3 = '生成台账表单成功\n'
        index = index + 1
        result = result + string3
        # 更新日报内容
        ts.fill_content()
        string4 = '日报内容更新成功\n'
        index = index + 1
        result = result + string4
        # 更新请求工单内容
        ts.request_content()
        string5 = '请求工单内容更新成功\n'
        index = index + 1
        result = result + string5
    finally:
        master = tk.Tk()
        master.withdraw()
        if index > 0:
            messagebox.showinfo(title="成功", message=result)
        if index != 6:
            messagebox.showerror(title="错误", message="仍有其他信息无法生成，请检查数据文件格式！")
